JBoss Community Archive (Read Only)

Teiid 8.1

DDL Metadata

Before Teiid 8.0, Dynamic VDB feature has capability to define source models and integrate data in those sources to by the end users. However there was no facility to define a View Layer where one can abstract integration of source layers to define logical views, such that the end user does not need to know about details about how those source layers are integrated.

Starting with Teiid 8.0 version, Dynamic VDB provides a facility to define models/schemas using DDL. Here is small example of how one can define a View inside the "-vdb.xml" file. See <metadata> element under <model>.

Example to show view definition
<model visible = "true" type = "VIRTUAL" name = "customers">
     <metadata import-type = "DDL"><![CDATA[
              CREATE VIEW PARTS (
                   PART_ID integer PRIMARY KEY,
                   PART_NAME varchar(255),
                   PART_COLOR varchar(30),
                   PART_WEIGHT varchar(255)
               ) AS
                  select a.id as PART_ID, a.name as PART_NAME, b.color as PART_COLOR, b.weight as PART_WEIGHT from modelA.part a, modelB.part b where a.id = b.id
     ]]>
     <metadata>
</model>

Another complete DDL based example, at the end of this section.

Dynamic Repositories

The declaration of metadata using DDL is supported out of the box, however user can extend MetadataRepository interface to plug-in their own metadata facilities with this new feature. For example you can write a hibernate based store that can feed the necessary metadata just as below DDL. Using this facility user can essentially define a full virtual database without the help of the Teiid Designer. This can lead to design of virtual databases that are more dynamic in nature that can be changed programatically. See Custom Metadata Repository for more information.

Metadata for Source Models

The DDL based schema is not constrained to be defined only for the view models, users can also define source models, where <model type="PHISICAL">. This model is restricted to define only FOREIGN TABLES, PUSHDOWN FUNCTIONS, NON-VIRTUAL PROCEDURES.

BNF notation for the Metadata

Create FOREIGN Table

A FOREIGN table is table that is defined on PHYSICAL model that represents a real relational table in source databases like Oracle, SQLServer etc. For relational databases, Teiid has capability to automatically retrieve the database schema information upon the deployment of the VDB, if one like to auto import the existing schema. However, user can use below FOREIGN table semantics, when they would like to explicitly define tables on PHYSICAL models or represent non-relational data as relational in custom translators.

images/author/download/attachments/53379300/create_table.jpg

Example:Create Foreign Table(Created on PHYSICAL model)
CREATE FOREIGN TABLE Customer (id integer PRIMARY KEY, firstname varchar(25), lastname varchar(25),  dob timestamp);

CREATE FOREIGN TABLE Order (id integer PRIMARY KEY, customerid integer, saledate date, amount decimal(25,4) CONSTRAINT FOREGIN KEY(customerid) REFERENCES Customer(id));

Create View

A view is a virtual table. A view contains rows and columns,like a real table. The fields in a view are fields from one or more real tables from the source or other view models. They can also be expressions made up multiple columns, or aggregated columns. When column definitions are not defined on the view table, they will be derived from the projected columns of the view's select transformation that is defined after the AS keyword.

You can add functions, JOIN statements and WHERE clauses to a view data as if the data were coming from one single table.

images/author/download/attachments/53379300/create_view.jpg

TABLE/VIEW OPTIONS: (the below are well known options, any others properties defined will be considered as extension metadata)

Property

Data Type or Allowed Values

Description

UUID

string

Unique identifier for View

MATERIALIZED

'TRUE'|'FALSE'

Defines if a table is materialized

MATERIALIZED_TABLE

'table.name'

If this view is being materialized to a external database, this defines the name of the table that is being materialized to

CARDINALITY

int

Costing information. Number of rows in the table. Used for planning purposes

UPDATABLE

'TRUE'|'FALSE'

Defines if the view is allowed to update or not

ANNOTATION

string

Description of the view

Example:Create View Table(Created on VIRTUAL model)
CREATE VIEW CustomerOrders (name varchar(50),  saledate date, amount decimal) OPTIONS (CARDINALITY 100, ANNOTATION 'Example')
  AS
  SELECT concat(c.firstname, c.lastname) as name, o.saledate as saledate, o.amount as amount FROM Customer C JOIN Order o ON c.id = o.customerid;

images/author/download/attachments/53379300/create_column.jpg

COLUMN_OPTIONS: (the below are well known options, any others properties defined will be considered as extension metadata)

Property

Data Type or Allowed Values

Description

UUID

string

A unique identifier for the column

NAMEINSOURCE

string

If this is a column name on the FOREIGN table, this value represents name of the column in source database, if omitted the column name is used when querying for data against the source

CASE_SENSITIVE

'TRUE'|'FALSE'

 

SELECTABLE

'TRUE'|'FALSE'

TRUE when this column is available for selection from the user query

UPDATABLE

'TRUE'|'FALSE'

Defines if the column is updatable.  Defaults to true if the view/table is updatable.

SIGNED

'TRUE'|'FALSE'

 

CURRENCY

'TRUE'|'FALSE'

 

FIXED_LENGTH

'TRUE'|'FALSE'

 

SEARCHABLE

'SEARCHABLE'|'UNSEARCHABLE'|'LIKE_ONLY'|'ALL_EXCEPT_LIKE'

column searchability, usually dictated by the data type

MIN_VALUE

 

 

MAX_VALUE

 

 

CHAR_OCTET_LENGTH

integer

 

ANNOTATION

string

 

NATIVE_TYPE

string

 

RADIX

integer

 

NULL_VALUE_COUNT

integer

costing information. Number of NULLS in this column

CONSTRAINTS

Constraints can be defined on table/view to define indexes and relationships to other tables/views. This information is used by the Teiid optimizer to plan queries or use the indexes in materialization tables to optimize the access to the data.

images/author/download/attachments/53379300/create_constraint.jpg

CONSTRAINTS are same as one can define on RDBMS.

Example of CONSTRAINTS
CREATE VIEW CustomerOrders (name varchar(50),  saledate date, amount decimal CONSTRAINTS
   INDEX (name, amount)
   ACCESSPATTERN (name)
   PRIMARY KEY ...

INSTEAD OF TRIGGERS

A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. Based on the select transformation's complexity some times INSTEAD OF TRIGGERS are automatically provided for the user when "UPDATABLE" OPTION on the view is set to "TRUE". However, using the CREATE TRIGGER mechanism user can provide/override the default behaviour.

images/author/download/attachments/53379300/create_trigger.jpg

Example:Define instead of trigger on View
CREATE TRIGGER ON CustomerOrders INSTEAD OF INSERT AS
   FOR EACH ROW
   BEGIN ATOMIC
   INSERT INTO Customer (...) VALUES (NEW.value ...);
   END

Create Procedure/Function

Using the below syntax, user can define a

  • Source Procedure - a stored procedure in source

  • Source Function - A function that is supported by the source, where Teiid will pushdown to source instead of evaluating in Teiid engine

  • Virtual Procedure - Similar to stored procedure, however this is defined using the Teiid's Procedure language and evaluated in the Teiid's engine.

  • Funtion/UDF - A user defined function, where can user define the calling semantics using below, however the function implementation is defined using a JAVA Class.1

images/author/download/attachments/53379300/create_procedure.jpg

FUNCTION OPTIONS:(the below are well known options, any others properties defined will be considered as extension metadata)

Property

Data Type or Allowed Values

Description

UUID

string

unique Identifier

NAMEINSOURCE

If this is source function/procedure the name in the physical source, if different from the logical name given above

ANNOTATION

string

Description of the function/procedure

CATEGORY

string

Function Category

DETERMINISM

  • NONDETERMINISTIC

  • COMMAND_DETERMINISTIC

  • SESSION_DETERMINISTIC

  • USER_DETERMINISTIC

  • VDB_DETERMINISTIC

  • DETERMINISTIC

NULL-ON-NULL

'TRUE'|'FALSE'

JAVA_CLASS

string

Java Class that defines the method in case of UDF

JAVA_METHOD

string

The Java method name on the above defined java class for the UDF implementation

VARARGS

'TRUE'|'FALSE'

Indicates that the last argument of the function can be repeated 0 to any number of times. default false.

AGGREGATE

'TRUE'|'FALSE'

Indicates the function is a user defined aggregate function. Properties specific to aggregates are listed below:

Note that NULL-ON-NULL, VARARGS, and all of the AGGREGATE properties are also valid relational extension metadata properties that can be used on source procedures marked as functions.

AGGREGATE FUNCTION OPTIONS:

Property

Data Type or Allowed Values

Description

ANALYTIC

'TRUE'|'FALSE'

indicates the aggregate function must be windowed. default false.

ALLOWS-ORDERBY

'TRUE'|'FALSE'

indicates the aggregate function supports an ORDER BY clause. default false

ALLOWS-DISTINCT

'TRUE'|'FALSE'

indicates the aggregate function supports the DISTINCT keyword. default false

DECOMPOSABLE

'TRUE'|'FALSE'

indicates the single argument aggregate function can be decomposed as agg(agg(x) ) over subsets of data. default false

USES-DISTINCT-ROWS

'TRUE'|'FALSE'

indicates the aggregate function effectively uses distinct rows rather than all rows. default false

Providing the JAR libraries

If you have defined a UDF (virtual) function, then it must be accompanied by its implementation in Java. To configure the Java library as dependency to the VDB, see Support for User-Defined Functions

PROCEDURE_OPTIONS:(the below are well known options, any others properties defined will be considered as extension metadata)

Property

Data Type or Allowed Values

Description

UUID

string

Unique Identifier

NAMEINSOURCE

string

In the case of source

ANNOTATION

string

Description of the procedure

UPDATECOUNT

int

if this procedure updates the underlying sources, what is the update count, when update count is >1 the XA protocol for execution is enforced

Example:Define Procedure
CREATE VIRTUAL PROCEDURE CustomerActivity(customerid integer) RETURNS (name varchar(25), activitydate date, amount decimal) AS
   BEGIN
   ...
   END

Options

images/author/download/attachments/53379300/create_options.jpg

Data Types

The following are the supported data types in the Teiid.

images/author/download/attachments/53379300/datatypes.png

Namespaces for Extension Metadata

When defining the extension metadata in the case of Custom Translators, the properties on tables/views/procedures/columns can define namespace for the properties such that they will not collide with the Teiid specific properties. The property should be prefixed with alias of the Namespace.

images/author/download/attachments/53379300/create_namespace.jpg

Example of Namespace
SET NAMESPACE 'http://custom.uri' AS foo

CREATE VIEW MyView (...) OPTIONS (foo:mycustom-prop 'anyvalue')

Full Example showing above DDL based metadata

Full Example VDB with View Definition
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="twitter" version="1">

    <description>Shows how to call Web Services</description>

    <property name="UseConnectorMetadata" value="cached" />

    <model name="twitter">
        <source name="twitter" translator-name="rest" connection-jndi-name="java:/twitterDS"/>
    </model>
    <model name="twitterview" type="VIRTUAL">
         <metadata type="DDL"><![CDATA[
             CREATE VIRTUAL PROCEDURE getTweets(query varchar) RETURNS (created_on varchar(25), from_user varchar(25), to_user varchar(25),
                 profile_image_url varchar(25), source varchar(25), text varchar(140)) AS
                select tweet.* from
	                (call twitter.invokeHTTP(action => 'GET', endpoint =>querystring('',query as "q"))) w,
	                XMLTABLE('results' passing JSONTOXML('myxml', w.result) columns
	                created_on string PATH 'created_at',
	                from_user string PATH 'from_user',
	                to_user string PATH 'to_user',
	                profile_image_url string PATH 'profile_image_url',
	                source string PATH 'source',
	                text string PATH 'text') tweet;
                CREATE VIEW Tweet AS select * FROM twitterview.getTweets;
        ]]> </metadata>
    </model>

    <translator name="rest" type="ws">
		<property name="DefaultBinding" value="HTTP"/>
		<property name="DefaultServiceMode" value="MESSAGE"/>
    </translator>
</vdb>
JBoss.org Content Archive (Read Only), exported from JBoss Community Documentation Editor at 2020-03-13 12:08:48 UTC, last content change 2012-08-14 17:30:04 UTC.